﻿using QG.FrontDesk.Core;
using QG.FrontDesk.DAL.IRepository;
using QG.FrontDesk.Model;
using System.Collections.Generic;
using System.Linq;

namespace QG.FrontDesk.DAL.Repository
{
    public class CompanyRepository : ICompanyRepository
    {
        /// <summary>
        /// 新增公司信息
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public int AddCompany(Company company)
        {
            string sql = @"INSERT INTO company(`comFullName`,`companyPhone`,`companyEmail`) 
                            VALUES(@comFullName,@companyPhone,@companyEmail);  SELECT @@identity";
            return DapperDBHelper<Company>.Query(sql, company);
        }

        /// <summary>
        /// 完善公司信息
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public int UpdateCompany(Company company)
        {
            string sql = "update company set `comForShort`=@comForShort,`companyLoGo`=@companyLoGo,`comWebsite`=@comWebsite,`cityId`=@cityId,`industryId`=@industryId,`comScale`=@comScale,`phaseId`=@phaseId,`oneRecommend`=@oneRecommend where `compId`=@compId";
            return DapperDBHelper<Company>.Execute(sql, company);
        }
        /// <summary>
        /// 完善公司创始人团队
        /// </summary>
        /// <param name="compId"></param>
        /// <param name="foundTeams"></param>
        /// <returns></returns>
        public int UpdateCompanyFound(List<FoundTeam> foundTeams)
        {
            string sql = @"INSERT INTO foundTeam(`foundName`,`currentPost`,`sinaMicroblog`,`foundRemark`,`compId`)
            VALUES(@foundName, @currentPost, @sinaMicroblog, @foundRemark,@compId)";
            return DapperDBHelper<FoundTeam>.CountExecute(sql, foundTeams);
        }
        /// <summary>
        /// 完善公司信息
        /// </summary>
        /// <param name="CompId"></param>
        /// <param name="productInfos"></param>
        /// <returns></returns>
        public int UpdateCompanyProduct(List<ProductInfo> productInfos)
        {
            string sql = @"INSERT INTO productInfo(`productName`,`productBill`,`productAddress`,`compId`)
                        VALUES  (@productName,@productBill,@productAddress,@compId)";
            return DapperDBHelper<ProductInfo>.CountExecute(sql, productInfos);
        }
        /// <summary>
        /// 完善公司简介
        /// </summary>
        /// <param name="remark"></param>
        /// <returns></returns>
        public int UpdateCompanyRemark(int CompId, string remark)
        {
            string sql = "update company set companyRemark=@companyRemark where CompId=@CompId";
            return DapperDBHelper<Company>.Execute(sql, new Company() { CompanyRemark = remark, CompId = CompId });
        }




        /// <summary>
        /// 完善公司标签
        /// </summary>
        /// <param name="labels"></param>
        /// <returns></returns>
        public int UpdateCompanyTabel(int compId, string[] labels)
        {
            List<Label> list = new List<Label>();
            List<Company_Label> company_Labels = new List<Company_Label>();
            //转为list
            foreach (var item in labels)
            {
                list.Add(new Label() { LabelName = item });
                company_Labels.Add(new Company_Label() { CompId = compId, LabelName = item });
            }
            //标签表新增--如果不存在就插入数据
            string sql = @"INSERT INTO label(labelname) 
                            SELECT @labelname FROM DUAL
                            WHERE NOT EXISTS(SELECT labelname FROM label WHERE labelname = @labelname)";
            DapperDBHelper<Label>.CountExecute(sql, list);
            //标签公司关联表新增
            sql = @"INSERT INTO `company_label` VALUES
                (NULL, @CompId, (SELECT labelId FROM label WHERE `labelName` = @Labelname))";
            return DapperDBHelper<Company_Label>.CountExecute(sql, company_Labels);
        }

        /// <summary>
        /// 得到所有公司信息
        /// </summary>
        /// <returns></returns>
        public List<Company> GetCompanyList(string fs, string ifs, string city)
        {
            List<Company> companys = null;
            string sql = @"SELECT * FROM company";
            if (!string.IsNullOrEmpty(fs) && !string.IsNullOrEmpty(ifs))
            {
                fs = "%" + fs + "%";
                ifs = "%" + ifs + "%";
                sql = "SELECT * FROM company WHERE phaseId IN (SELECT phaseId FROM developmentPhase WHERE phaseName LIKE @phaseName) AND industryId IN (SELECT industryId FROM industry WHERE industryName LIKE @industryName)";
                companys = DapperDBHelper<Company>.QueryList(sql, new { phaseName = fs, industryName =ifs});
            }
            else if (!string.IsNullOrEmpty(fs))
            {
                string word = "%" + fs + "%";
                sql += " WHERE phaseId in (SELECT phaseId FROM developmentPhase WHERE phaseName LIKE @word)";
                companys = DapperDBHelper<Company>.QueryList(sql, new { word = word });
            }
            else if (!string.IsNullOrEmpty(ifs))
            {
                ifs = "%" + ifs + "%";
                sql = "SELECT * FROM company WHERE industryId IN (SELECT industryId FROM industry WHERE industryName LIKE @industryName)";
                companys = DapperDBHelper<Company>.QueryList(sql, new { industryName = ifs });
            }
            else if (!string.IsNullOrEmpty(city))
            {
                sql = "SELECT * FROM company WHERE cityId =(SELECT cityId FROM `cityinfo` WHERE `cityName`=@cityName)";
                companys = DapperDBHelper<Company>.QueryList(sql, new { cityName = city });
            }
            else
            {
                companys = DapperDBHelper<Company>.QueryList(sql, null);
            }
            foreach (var company in companys)
            {
                var compId = company.CompId;
                //查找对应的标签
                sql = @"SELECT * FROM company_Label
                    INNER JOIN label ON company_Label.`labelId`= label.`labelId`
                    WHERE compId = @CompId";
                company.Company_label = DapperDBHelper<Company_Label>.QueryList(sql, new { CompId = compId });
                //查找对应的产品信息
                sql = @"SELECT * FROM productInfo WHERE compId= @CompId";
                company.ProductInfo = DapperDBHelper<ProductInfo>.QueryList(sql, new { CompId = compId });
                //查找对应的简历信息
                sql = "SELECT * FROM Positioninfo WHERE compId=@CompId";
                company.PositionInfo = DapperDBHelper<PositionInfo>.QueryList(sql, new { CompId = compId });
                //查找公司创始人团队
                sql = "SELECT * FROM FoundTeam WHERE compId=@CompId";
                company.FoundTeam = DapperDBHelper<FoundTeam>.QueryList(sql, new { CompId = compId });
                //查找公司投资机构
                sql = "SELECT * FROM industry WHERE industryId=@industryId";
                company.Industry = DapperDBHelper<Industry>.QueryFirstOrDefault(sql, new { industryId = company.IndustryId });
                //查找公司对应的城市
                sql = "SELECT * FROM cityinfo WHERE cityId=@cityId";
                company.CityInfo = DapperDBHelper<Cityinfo>.QueryFirstOrDefault(sql, new { cityId = company.CityId });
            }
            return companys;
        }

        /// <summary>
        /// 得到公司信息
        /// </summary>
        /// <param name="compId"></param>
        /// <returns></returns>
        public Company GetCompanyInfo(int compId)
        {
            string sql = @"SELECT * FROM company
                        where CompId=@CompId";

            Company company = DapperDBHelper<Company>.QueryFirst(sql, new { CompId = compId });
            //查找对应的标签
            sql = @"SELECT * FROM company_Label
                    INNER JOIN label ON company_Label.`labelId`= label.`labelId`
                    WHERE compId = @CompId";
            company.Company_label = DapperDBHelper<Company_Label>.QueryList(sql, new { CompId = compId });
            //查找对应的产品信息
            sql = @"SELECT * FROM productInfo WHERE compId= @CompId";
            company.ProductInfo = DapperDBHelper<ProductInfo>.QueryList(sql, new { CompId = compId });
            //查找对应的简历信息
            sql = "SELECT * FROM Positioninfo WHERE compId=@CompId";
            company.PositionInfo = DapperDBHelper<PositionInfo>.QueryList(sql, new { CompId = compId });
            //查询简历对应的城市和职位名称
            foreach (var item in company.PositionInfo)
            {
                sql = "SELECT cityName FROM cityInfo WHERE cityId=@cityId";
                item.Cityinfo = DapperDBHelper<Cityinfo>.QueryFirstOrDefault(sql, new { cityId = item.CityId });

                sql = "SELECT NAME FROM postName WHERE postNameId=@postNameId";
                item.Postname = DapperDBHelper<PostName>.QueryFirstOrDefault(sql, new { postNameId = item.PostNameId });
            }
            //查找公司创始人团队
            sql = "SELECT * FROM FoundTeam WHERE compId=@CompId";
            company.FoundTeam = DapperDBHelper<FoundTeam>.QueryList(sql, new { CompId = compId });
            //查找行业领域
            sql = "SELECT * FROM industry WHERE industryId=@industryId";
            company.Industry = DapperDBHelper<Industry>.QueryFirstOrDefault(sql, new { industryId = company.IndustryId });
            //查找城市
            sql = "SELECT * FROM cityInfo WHERE cityId=@cityId";
            company.CityInfo = DapperDBHelper<Cityinfo>.QueryFirstOrDefault(sql, new { cityId = company.CityId });
            //查找发展阶段
            sql = "SELECT * FROM developmentPhase WHERE phaseId=@phaseId";
            company.DevelopmentPhase = DapperDBHelper<DevelopmentPhase>.QueryFirstOrDefault(sql, new { phaseId = company.PhaseId });
            return company;
        }
        /// <summary>
        /// 修改标签详情页
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="labels"></param>
        /// <returns></returns>
        public int UpdateLabelaByDetails(int companyId, string[] labels)
        {
            List<Label> list = new List<Label>();
            List<Company_Label> company_Labels = new List<Company_Label>();
            foreach (var item in labels)
            {
                list.Add(new Label() { LabelName = item });
                company_Labels.Add(new Company_Label() { CompId = companyId, LabelName = item });

            }
            //如果表中没有该数据,则插入
            string sql = @"INSERT INTO label(labelname) 
            SELECT @labelname FROM DUAL
            WHERE NOT EXISTS(SELECT labelname FROM label WHERE labelname = @labelname)";
            DapperDBHelper<Label>.CountExecute(sql, list);
            //查询对应公司标签表
            sql = "SELECT * FROM company_label  INNER JOIN label ON label.`labelId`=company_label.`labelId` WHERE compId=@compId";
            List<Company_Label> labels1 = DapperDBHelper<Company_Label>.QueryList(sql, new { compId = companyId });

            Dictionary<string, object> dic = new Dictionary<string, object>();

            //需要删除的标签集合
            List<Company_Label> deleteLabel = labels1.Where(a => !company_Labels.Exists(b => b.LabelName == a.LabelName)).ToList();
            //需要新增的标签集合
            List<Company_Label> addLabel = company_Labels.Where(a => !labels1.Exists(b => b.LabelName == a.LabelName)).ToList();

            //给需要新增的标签集合赋Id
            sql = "SELECT labelId FROM label WHERE labelName=@labelName";
            foreach (var item in addLabel)
            {
                item.LabelId = DapperDBHelper<Label>.Query(sql, new { labelName = item.LabelName });
            }

            //删除标签公司关联表数据
            sql = "DELETE FROM company_label WHERE compId=@compId AND labelId=@labelId";
            dic.Add(sql, deleteLabel);
            //新增标签公司关联表数据
            sql = "INSERT INTO company_Label VALUES(NULL,@compId,@labelId)";
            dic.Add(sql, addLabel);
            return DapperDBHelper<Company_Label>.DictExcute(dic);
        }
        /// <summary>
        /// 修改公司地址,主页,规模
        /// </summary>
        /// <param name="CityName"></param>
        /// <param name="company"></param>
        /// <returns></returns>
        public int UpdateCityIndustryByDetails(Company company)
        {
            string sql = "update company set cityId=(select cityId FROM cityInfo WHERE cityName=@cityName),comScale=@comScale,comWebsite=@comWebsite where compId=@compId";
            return DapperDBHelper<Company>.Execute(sql, new { cityName = company.CityInfo.CityName, comScale = company.ComScale, comWebsite = company.ComWebsite, compId = company.CompId });
        }
        /// <summary>
        /// 修改公司介绍
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public int UpdateCityRemarkByDetails(Company company)
        {
            string sql = "UPDATE company SET companyRemark=@companyRemark WHERE compId=@compId";
            return DapperDBHelper<Company>.Execute(sql, new { companyRemark = company.CompanyRemark, compId = company.CompId });
        }
        /// <summary>
        /// 修改公司产品
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public int UpdateProductByDetails(ProductInfo productInfo)
        {
            string sql = "INSERT INTO productinfo VALUE(NULL,@productBill,@productName,@productAddress,@productRemark,@compId)";
            return DapperDBHelper<ProductInfo>.Execute(sql, productInfo);

        }
        /// <summary>
        /// 公司登录
        /// </summary>
        /// <param name="Email"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public Company GetCompany(string Email, string pwd)
        {
            string sql = "SELECT * FROM company WHERE companyEmail=@Email AND PASSWORD=@pwd";
            return DapperDBHelper<Company>.QueryFirstOrDefault(sql, new { Email = Email, pwd = pwd });
        }
        /// <summary>
        /// 设置密码
        /// </summary>
        /// <param name="password"></param>
        /// <param name="compId"></param>
        /// <returns></returns>
        public int UpdatePwd(string password, int compId)
        {
            string sql = "UPDATE company SET PASSWORD=@password WHERE compId=@compId";
            return DapperDBHelper<Company>.Execute(sql, new { password = password, compId = compId });
        }
    }
}
